import sqlite3
import json
import os
from datetime import datetime
from collections import defaultdict
from jinja2 import Environment, FileSystemLoader
import pdfkit # Make sure you have wkhtmltopdf installed and accessible

# --- 辅助函数：从数据库获取未结算数据 (与之前提供的一致，稍作优化以适应模板传入) ---
def get_unsettled_data_for_purchaser(purchaser_name: str) -> dict:
    """
    Retrieves unsettled order data for a specific purchaser, formatted for the HTML template.

    Args:
        purchaser_name (str): The name of the purchasing company.

    Returns:
        dict: A dictionary containing purchaser_name and a list of unsettled_orders,
              or an empty structure if no data is found.
    """
    db_path = os.path.join("data", "aidemo.db")
    conn = None
    # Initialize the structure for a single purchaser
    purchaser_data_output = {
        "purchaser_name": purchaser_name,
        "unsettled_orders": []
    }
    # Temporary dict to build nested orders within this purchaser
    temp_orders_dict = defaultdict(lambda: {"order_info": {}, "order_details": []})

    try:
        conn = sqlite3.connect(db_path)
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()

        query = """
            SELECT
                o.order_id,
                o.purchaser,
                o.total_amount,
                o.order_time,
                o.delivery_time,
                o.is_settled,
                od.detail_id,
                od.quantity AS ordered_quantity,
                od.price AS ordered_price,
                od.amount AS ordered_amount,
                p.product_id,
                p.product_name,
                p.product_type,
                p.specification,
                p.price AS current_product_price,
                p.stock_quantity,
                p.description
            FROM
                orders AS o
            JOIN
                order_details AS od ON o.order_id = od.order_id
            JOIN
                products AS p ON od.product_id = p.product_id
            WHERE
                o.is_settled = 0 AND o.purchaser = ?
            ORDER BY
                o.order_time, od.detail_id;
        """
        cursor.execute(query, (purchaser_name,))
        rows = cursor.fetchall()

        if not rows:
            return purchaser_data_output # Return with empty unsettled_orders list

        for row in rows:
            order_id = row['order_id']

            if not temp_orders_dict[order_id]["order_info"]:
                temp_orders_dict[order_id]["order_info"] = {
                    "order_id": row['order_id'],
                    "total_amount": row['total_amount'],
                    "order_time": row['order_time'],
                    "delivery_time": row['delivery_time'],
                    "is_settled": bool(row['is_settled'])
                }

            product_info = {
                "product_id": row['product_id'],
                "product_name": row['product_name'],
                "product_type": row['product_type'],
                "specification": row['specification'],
                "current_price": row['current_product_price'],
                "stock_quantity": row['stock_quantity'],
                "description": row['description']
            }

            order_detail = {
                "detail_id": row['detail_id'],
                "quantity": row['ordered_quantity'],
                "price_at_order": row['ordered_price'],
                "amount": row['ordered_amount'],
                "product_info": product_info
            }
            temp_orders_dict[order_id]["order_details"].append(order_detail)
        
        # Convert the defaultdict of orders into a list for the template
        for order_key in sorted(temp_orders_dict.keys()): # Sort orders by ID/time for consistent output
            purchaser_data_output["unsettled_orders"].append(temp_orders_dict[order_key])

    except sqlite3.Error as e:
        print(f"Database error: {e}")
    except Exception as e:
        print(f"An unexpected error occurred: {e}")
    finally:
        if conn:
            conn.close()

    return purchaser_data_output


# --- 主函数：生成 PDF 结算单 ---
def generate_settlement_pdf(purchaser_name: str, template_path="templates", template_file="statement_template.html", output_dir="settlement_pdfs"):
    """
    Generates a PDF settlement statement for a given purchaser.

    Args:
        purchaser_name (str): The name of the purchaser.
        template_path (str): Directory where the Jinja2 template is located.
        template_file (str): The name of the HTML template file.
        output_dir (str): Directory to save the generated PDF.
    """
    # 确保输出目录存在
    if not os.path.exists(output_dir):
        os.makedirs(output_dir)

    # 1. 从数据库获取数据
    data_for_template = get_unsettled_data_for_purchaser(purchaser_name)

    # 如果没有找到数据，可以提前退出
    if not data_for_template or not data_for_template.get("unsettled_orders"):
        print(f"No unsettled orders found for {purchaser_name}. PDF not generated.")
        return

    # 2. 准备 Jinja2 环境和渲染模板
    env = Environment(loader=FileSystemLoader(template_path))
    template = env.get_template(template_file)

    # 准备模板中需要的数据
    # 为了模板渲染方便，直接将 data_for_template 的内容解包传入
    # 例如：purchaser_name, unsettled_orders
    template_vars = {
        "purchaser_name": data_for_template["purchaser_name"],
        "unsettled_orders": data_for_template["unsettled_orders"],
        "current_date": datetime.now().strftime('%Y年%m月%d日'),
        "statement_id": f"JSD-{datetime.now().strftime('%Y%m%d%H%M%S')}-{purchaser_name[:4].replace(' ', '')}" # 示例结算单编号
    }
    
    html_output = template.render(template_vars)

    # 3. 使用 pdfkit 将 HTML 转换为 PDF
    output_filename = os.path.join(output_dir, f"结算单_{purchaser_name}_{datetime.now().strftime('%Y%m%d%H%M%S')}.pdf")
    
    config = pdfkit.configuration() 

    options = {
        'enable-local-file-access': None, # 允许访问本地文件，如图片
        'page-size': 'A4',
        'margin-top': '20mm',
        'margin-right': '20mm',
        'margin-bottom': '20mm',
        'margin-left': '20mm',
        'encoding': "UTF-8"
    }

    try:
        pdfkit.from_string(html_output, output_filename, configuration=config, options=options)
        print(f"PDF结算单已成功生成到: {output_filename}")
        return output_filename
    except Exception as e:
        raise  RuntimeError(f"生成 PDF 时发生错误: {e}.")
       
# --- 运行示例 ---
if __name__ == "__main__":
    # 在运行此脚本之前，请确保已执行了数据库初始化脚本
    # 并且已经创建了 '上海白金酒店管理有限公司' 和 '北京四季酒店' 的未结算订单。

    # 创建一个示例订单以确保有数据可查
    # from your_order_creation_script import create_order # 假设你的订单创建函数在另一个文件
    # create_order("示例客户A", [{'product_id': '001', 'quantity': 2}])
    # create_order("示例客户B", [{'product_id': '002', 'quantity': 3}, {'product_id': '003', 'quantity': 1}])

    # 示例1：生成 '上海白金酒店管理有限公司' 的结算单
    generate_settlement_pdf("北京四季酒店")
